﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SWDataDB.Models;
using SWDataDB.DAL;
using System.Data;
/*using SWDataDB.BLL.PublicClass;*/

namespace SWDataDB.BLL
{
    public class SpaceQuery
    {
        public SpaceQuery() { }

        /// <summary>
        /// 根据矩形拉框的范围查找范围内的项目著录表
        /// </summary>
        /// <param name="startLat">左上角纬度</param>
        /// <param name="startLon">左上角经度</param>
        /// <param name="endLat">右下角纬度</param>
        /// <param name="endLon">右下角经度</param>
        /// <returns></returns>
        public IList<ZL_TBL> SpaceAdvanceQuery(int startLat,int startLon,int endLat,int endLon)
        {
            IList<ZL_TBL> zlLst = new List<ZL_TBL>();

            return zlLst;
        }


        /// <summary>
        /// 根据ZL_FILE_ID查询钻孔数据，获取数据有(ID,X,Y)
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public DataSet SelectSpaceInfoByFileID(string file_id)
        {
            string strSql = "SELECT ID,(START_LONGITUDE+END_LONGITUDE)/2 AS X,(START_LATITUDE+END_LATITUDE)/2 AS Y FROM ZL_TBL WHERE FILE_ID ='";
            strSql += file_id + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        } 

		/// <summary>
        /// 根据rect查询钻孔数据，获取数据有(ID,FILE_ID)
        /// </summary>
        public DataSet SelectFileIDByRect(double minx, double miny, double maxx, double maxy)
        {
            string strSql = "SELECT ID,FILE_ID,TITLE,START_LATITUDE,START_LONGITUDE FROM ZL_TBL WHERE ";
            StringBuilder sbSql = new StringBuilder(strSql);
            sbSql.AppendFormat("(START_LONGITUDE+END_LONGITUDE)/2>={0} AND (START_LONGITUDE+END_LONGITUDE)/2<={1} ", minx, maxx);
            sbSql.AppendFormat("(START_LATITUDE+END_LATITUDE)/2>={0} AND (START_LATITUDE+END_LATITUDE)/2<={1} ", miny, maxy);

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        /// <summary>
        /// 空间查询接口（郑华伟需求），只返回档号题名和起始和结束经纬度
        /// </summary>
        /// <returns></returns>
        public DataSet ZLInfoSpaceQueryKC()
        {
            StringBuilder sbSql = new StringBuilder();

            sbSql.AppendFormat(" 1=1");

            ZLInfo zlInfo = new ZLInfo();
            return zlInfo.GetInfoForSpaceQueryByASC(sbSql.ToString());
        }

        /// <summary>
        /// 空间查询接口（郑华伟需求），只返回档号题名和起始经纬度
        /// </summary>
        /// <param name="minx">开始经度</param>
        /// <param name="miny">开始纬度</param>
        /// <param name="maxx">结束经度</param>
        /// <param name="maxy">结束纬度</param>
        /// <returns></returns>
        public DataSet ZLInfoSpaceQueryKC(double minx, double miny, double maxx, double maxy)
        {
            StringBuilder sbSql = new StringBuilder();

            int startLon = PublicClass.DBFtoDMS(minx);
            int startLat = PublicClass.DBFtoDMS(miny);
            int endLon = PublicClass.DBFtoDMS(maxx);
            int endLat = PublicClass.DBFtoDMS(maxy);

            string temp1 = startLon.ToString();
            string temp2 = endLon.ToString();
            if (!string.IsNullOrEmpty(temp1) && !string.IsNullOrEmpty(temp2))
                sbSql.AppendFormat(" (START_LONGITUDE+END_LONGITUDE)/2>={0} AND (START_LONGITUDE+END_LONGITUDE)/2<={1} AND ", startLon.ToString(), endLon.ToString());

            string temp3 = startLat.ToString();
            string temp4 = endLat.ToString();
            if (!string.IsNullOrEmpty(temp3) && !string.IsNullOrEmpty(temp4))
                sbSql.AppendFormat(" (START_LATITUDE+END_LATITUDE)/2>={0} AND (START_LATITUDE+END_LATITUDE)/2<={1} AND ", startLat.ToString(), endLat.ToString());

            sbSql.AppendFormat("1=1 AND ");

            sbSql.Remove(sbSql.Length - 5, 4);

            ZLInfo zlInfo = new ZLInfo();
            return zlInfo.GetInfoForSpaceQueryByASC(sbSql.ToString());
        }
    }
}
